package com.chaopei.dao.impl;

import com.chaopei.dao.StuCourDao;
import com.chaopei.entity.StuCour;
import com.chaopei.tools.MyTools;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author Corday 天涯
 * @Date 2022/3/25 9:50
 */
public class StuCourDaoImpl implements StuCourDao {
    @Override
    public List<StuCour> selectAllStuCour() {
        Connection connection = MyTools.getConn();
//        以下是数据库的操作
        PreparedStatement ps = null;
        List<StuCour> scs = null;
        try {
            String sql = "select sid,name,birth,\n" +
                    "max(case cname when '英语' then score else 0 end)eng,\n" +
                    "max(case cname when '高数' then score else 0 end)math, \n" +
                    "max(case cname when '线性代数' then score else 0 end)line, \n" +
                    "max(case cname when '网络基础' then score else 0 end)web, \n" +
                    "max(case cname when 'Java' then score else 0 end)java,\n" +
                    "max(case cname when 'python' then score else 0 end)python \n" +
                    "from stu_cour_sel_v group by sid;";
            //生成数据库操作对象
            ps = connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            scs = new ArrayList<>();
            while (rs.next()) {
                int sid = rs.getInt("sid");
                String name = rs.getString("name");
                Date birth = rs.getDate("birth");
                double eng = rs.getDouble("eng");
                double math = rs.getDouble("math");
                double line = rs.getDouble("line");
                double web = rs.getDouble("web");
                double java = rs.getDouble("java");
                double python = rs.getDouble("python");
                StuCour sc = new StuCour(sid, name, birth, eng, math, line, web, java, python);
                scs.add(sc);
            }
            //关闭数据库连接
            MyTools.closeConn(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return scs;
    }
}
